package com.tuanzi.loan.business.repository.galaxycard.impl;

import static com.tuanzi.loan.generated.jooq.loan.tables.CommProductChannel.COMM_PRODUCT_CHANNEL;
import static com.tuanzi.loan.generated.jooq.loan.tables.CommProductPrdid.COMM_PRODUCT_PRDID;
import static com.tuanzi.loan.generated.jooq.loan.tables.LoanDistributionRecord.LOAN_DISTRIBUTION_RECORD;
import static com.tuanzi.loan.generated.jooq.loan.tables.LoanGalaxyCardStat.LOAN_GALAXY_CARD_STAT;

import java.sql.Date;
import java.sql.Timestamp;
import java.util.*;

import org.jooq.*;
import org.jooq.impl.DSL;
import org.jooq.impl.TableImpl;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Repository;

import com.tuanzi.loan.business.repository.galaxycard.GalaxyCardDistributionStatRepository;
import com.tuanzi.loan.core.repository.JOOQBaseRepositoryPlus;
import com.tuanzi.loan.vo.request.galaxycard.DistributionStatSearchRequest;
import com.tuanzi.loan.vo.response.galaxycard.GalaxyCardDistributionStatSearchResponse;

@Repository
public class GalaxyCardDistributionStatRepositoryImpl extends JOOQBaseRepositoryPlus implements GalaxyCardDistributionStatRepository {
    private static final String ALL_FIELD = "'全部'";
    private static final String PRODUCT_ID = "product_id";
    private static final String CHANNEL_ID = "channel_id";
    private static final String PLATFORM = "platform";
    private static final String CREATE_DATE = "create_date";
    private static final String PV = "pv";
    private static final String UV = "uv";

    private static final int SHARE = 3;
    private static final int VISIT = 4;
    private static final int CLICK = 5;
    private static final int APPLY = 6;
    private static final int PAYED = 7;

    private static Map<Integer, String> product;
    private static Map<Integer, String> channel;
    private static Map<String , String> platform;

    private static Long cacheTime = System.currentTimeMillis();

    public synchronized Map<String, Integer> getProduct(){
        if (Objects.isNull(product)){
            Table<Record1<Integer>> sp = dslContext.selectDistinct(LOAN_GALAXY_CARD_STAT.PRODUCT_ID).from(LOAN_GALAXY_CARD_STAT).asTable("SP");
            Result<Record2<String,Integer>> result = dslContext.selectDistinct(COMM_PRODUCT_PRDID.NAME, COMM_PRODUCT_PRDID.PRDID)
                    .from(COMM_PRODUCT_PRDID)
                    .join(sp).on(sp.field(LOAN_GALAXY_CARD_STAT.PRODUCT_ID).eq(COMM_PRODUCT_PRDID.PRDID))
                    .fetch();
            product = result.intoMap(COMM_PRODUCT_PRDID.PRDID, COMM_PRODUCT_PRDID.NAME);
        }
        return revert(product);
    }

    public synchronized Map<String, Integer> getChannel(){
        if (Objects.isNull(channel)){
            Table<Record1<Integer>> sp = dslContext.selectDistinct(LOAN_GALAXY_CARD_STAT.CHANNEL_ID).from(LOAN_GALAXY_CARD_STAT).asTable("SP");
            Result<Record2<String,Integer>> result = dslContext.select(COMM_PRODUCT_CHANNEL.CHANNEL_NAME, COMM_PRODUCT_CHANNEL.CHANNEL_CODE)
                    .from(COMM_PRODUCT_CHANNEL)
                    .join(sp).on(sp.field(LOAN_GALAXY_CARD_STAT.CHANNEL_ID).eq(COMM_PRODUCT_CHANNEL.CHANNEL_CODE))
                    .groupBy(COMM_PRODUCT_CHANNEL.CHANNEL_CODE)
                    .orderBy(COMM_PRODUCT_CHANNEL.ID.desc())
                    .fetch();
            channel = result.intoMap(COMM_PRODUCT_CHANNEL.CHANNEL_CODE,COMM_PRODUCT_CHANNEL.CHANNEL_NAME);
        }
        return revert(channel);
    }

    public synchronized Map<String,String> getPlatform(){
        if (Objects.isNull(platform)){
            platform = new HashMap<>();
            platform.put("ios","IOS");
            platform.put("android","安卓");
        }
        return revert(platform);
    }

    public void cleanCache(){
        product = null;
        channel = null;
    }

    private <K,V> Map<V,K> revert(Map<K,V> map){
        Map<V,K> revertMap = new HashMap<>();
        for (K key: map.keySet()){
            revertMap.put(map.get(key), key);
        }
        return revertMap;
    }

    /**
     * DistributionStat查询
     *
     * @param searchRequest 请求信息
     * @param pageable      分页信息
     * @return 响应列表
     */
    @Override
    @SuppressWarnings("unchecked")
    public Page<GalaxyCardDistributionStatSearchResponse> list(DistributionStatSearchRequest searchRequest, Pageable pageable) {
        // 初始化缓存使用
        getProduct();
        getChannel();

        Table shareTable = generateSelect(searchRequest, SHARE).asTable("share");
        Table visitTable = generateSelect(searchRequest, VISIT).asTable("visit");
        Table clickTable = generateSelect(searchRequest, CLICK).asTable("click");
        Table applyTable = generateSelect(searchRequest, APPLY).asTable("apply");
        Table payedTable = generateSelect(searchRequest).asTable("payed");

        Integer count = dslContext.selectCount().from(shareTable).fetchOne(0, Integer.class);

        shareTable = generateSelect(searchRequest, SHARE).asTable("share");
        SelectQuery selectQuery = dslContext.select(
                shareTable.field(CREATE_DATE),
                shareTable.field(PRODUCT_ID),
                shareTable.field(CHANNEL_ID),
                shareTable.field(PLATFORM),
                DSL.field(UV + SHARE).as("uv_1"),
                DSL.field(UV + VISIT).as("uv_2"),
                DSL.field(UV + CLICK).as("uv_3"),
                DSL.field(UV + APPLY).as("uv_4"),
                DSL.field(UV + PAYED).as("uv_5")
        )
                .from(shareTable)
                .leftJoin(visitTable).on(visitTable.field(CREATE_DATE).eq(shareTable.field(CREATE_DATE)))
                    .and(visitTable.field(PRODUCT_ID).eq(shareTable.field(PRODUCT_ID)))
                    .and(visitTable.field(CHANNEL_ID).eq(shareTable.field(CHANNEL_ID)))
                    .and(visitTable.field(PLATFORM).eq(shareTable.field(PLATFORM)))
                .leftJoin(clickTable).on(clickTable.field(CREATE_DATE).eq(shareTable.field(CREATE_DATE)))
                    .and(clickTable.field(PRODUCT_ID).eq(shareTable.field(PRODUCT_ID)))
                    .and(clickTable.field(CHANNEL_ID).eq(shareTable.field(CHANNEL_ID)))
                    .and(clickTable.field(PLATFORM).eq(shareTable.field(PLATFORM)))
                .leftJoin(applyTable).on(applyTable.field(CREATE_DATE).eq(shareTable.field(CREATE_DATE)))
                    .and(applyTable.field(PRODUCT_ID).eq(shareTable.field(PRODUCT_ID)))
                    .and(applyTable.field(CHANNEL_ID).eq(shareTable.field(CHANNEL_ID)))
                    .and(applyTable.field(PLATFORM).eq(shareTable.field(PLATFORM)))
                .leftJoin(payedTable).on(payedTable.field(CREATE_DATE).eq(shareTable.field(CREATE_DATE)))
                    .and(payedTable.field(PRODUCT_ID).eq(shareTable.field(PRODUCT_ID)))
                    .and(payedTable.field(CHANNEL_ID).eq(shareTable.field(CHANNEL_ID)))
                    .and(payedTable.field(PLATFORM).eq(shareTable.field(PLATFORM)))
                .getQuery();
        return search(selectQuery, pageable, new Long(count), record -> {
            GalaxyCardDistributionStatSearchResponse response = new GalaxyCardDistributionStatSearchResponse();
            response.setCreateTime(record.get(CREATE_DATE, String.class));

            String productStr = record.get(PRODUCT_ID, String.class);
            if (ALL_FIELD.equals(productStr))
                productStr = ALL_FIELD;
            else {
                String temp = product.get(record.get(PRODUCT_ID, Integer.class));
                if (Objects.nonNull(temp))
                    productStr = temp;
            }
            response.setProductId(productStr);

            String channelStr = record.get(CHANNEL_ID, String.class);
            if (ALL_FIELD.equals(channelStr))
                channelStr = ALL_FIELD;
            else {
                String temp = channel.get(record.get(CHANNEL_ID, Integer.class));
                if (Objects.nonNull(temp))
                    channelStr = temp;
            }
            response.setChannelId(channelStr);

            response.setPlatform(record.get(PLATFORM, String.class));

            Integer tempCount = record.get("uv_1", Integer.class);
            response.setShareCount(Objects.isNull(tempCount) ? 0 : tempCount);
            tempCount = record.get("uv_2", Integer.class);
            response.setVisitCount(Objects.isNull(tempCount) ? 0 : tempCount);
            tempCount = record.get("uv_3", Integer.class);
            response.setClickCount(Objects.isNull(tempCount) ? 0 : tempCount);
            tempCount = record.get("uv_4", Integer.class);
            response.setApplyCount(Objects.isNull(tempCount) ? 0 : tempCount);
            tempCount = record.get("uv_5", Integer.class);
            response.setPayedCount(Objects.isNull(tempCount) ? 0 : tempCount);
            return response;
        });
    }

    private SelectQuery<Record> generateSelect(DistributionStatSearchRequest searchRequest){
        Condition condition = DSL.trueCondition();

        List<SelectField<?>> selectFields = new ArrayList<>();
        selectFields.add(DSL.field("date_format(`create_time`,'%y-%m-%d')").as(CREATE_DATE));
        selectFields.add(DSL.count(LOAN_DISTRIBUTION_RECORD.ID).as(UV + PAYED));

        List<GroupField> groupFields = new ArrayList<>();
        groupFields.add(DSL.field(CREATE_DATE));
        // 处理参数
        if (Objects.nonNull(searchRequest.getProductId()) && !searchRequest.getProductId().isEmpty()) {
            selectFields.add(LOAN_DISTRIBUTION_RECORD.PRODUCT.as(PRODUCT_ID));
            groupFields.add(LOAN_DISTRIBUTION_RECORD.PRODUCT);
            condition.and(LOAN_DISTRIBUTION_RECORD.PRODUCT.in(searchRequest.getProductId()));
        }
        else selectFields.add(DSL.field(ALL_FIELD).as(PRODUCT_ID));

        if (Objects.nonNull(searchRequest.getChannelId()) && !searchRequest.getChannelId().isEmpty()){
            selectFields.add(LOAN_DISTRIBUTION_RECORD.CHANNEL.as(CHANNEL_ID));
            groupFields.add(LOAN_DISTRIBUTION_RECORD.CHANNEL);
            condition.and(LOAN_DISTRIBUTION_RECORD.CHANNEL.in(searchRequest.getChannelId()));
        }
        else
            selectFields.add(DSL.field(ALL_FIELD).as(CHANNEL_ID));

        if (Objects.nonNull(searchRequest.getPlatform()) && !searchRequest.getPlatform().isEmpty()){
            selectFields.add(LOAN_DISTRIBUTION_RECORD.PLATFORM.as(PLATFORM));
            groupFields.add(LOAN_DISTRIBUTION_RECORD.PLATFORM);
            condition.and(LOAN_DISTRIBUTION_RECORD.PLATFORM.in(searchRequest.getPlatform()));
        }
        else
            selectFields.add(DSL.field(ALL_FIELD).as(PLATFORM));

        if (Objects.nonNull(searchRequest.getStartTime()))
            condition.and(LOAN_DISTRIBUTION_RECORD.CREATE_TIME.ge(new Timestamp(searchRequest.getStartTime().getTime())));
        if (Objects.nonNull(searchRequest.getEndTime()))
            condition.and(LOAN_DISTRIBUTION_RECORD.CREATE_TIME.le(new Timestamp(searchRequest.getEndTime().getTime())));

        return dslContext.select(selectFields)
                .from(LOAN_DISTRIBUTION_RECORD)
                .where(condition)
                .groupBy(groupFields)
                .orderBy(LOAN_DISTRIBUTION_RECORD.CREATE_TIME.desc())
                .getQuery();
    }

    private SelectQuery<Record> generateSelect(DistributionStatSearchRequest searchRequest, int type){
        Condition condition = DSL.trueCondition();

        List<SelectField<?>> selectFields = new ArrayList<>();
        selectFields.add(LOAN_GALAXY_CARD_STAT.CREATE_DATE.as(CREATE_DATE));
//        selectFields.add(DSL.count(LOAN_GALAXY_CARD_STAT.PV.as(PV)));
        selectFields.add(DSL.sum(LOAN_GALAXY_CARD_STAT.UV).as(UV + type));

        List<GroupField> groupFields = new ArrayList<>();
        groupFields.add(LOAN_GALAXY_CARD_STAT.CREATE_DATE);
        // 处理参数
        if (Objects.nonNull(searchRequest.getProductId()) && !searchRequest.getProductId().isEmpty()) {
            selectFields.add(LOAN_GALAXY_CARD_STAT.PRODUCT_ID.as(PRODUCT_ID));
            groupFields.add(LOAN_GALAXY_CARD_STAT.PRODUCT_ID);
            condition = condition.and(LOAN_GALAXY_CARD_STAT.PRODUCT_ID.in(searchRequest.getProductId()));
        }
        else selectFields.add(DSL.field(ALL_FIELD).as(PRODUCT_ID));

        if (Objects.nonNull(searchRequest.getChannelId()) && !searchRequest.getChannelId().isEmpty()){
            selectFields.add(LOAN_GALAXY_CARD_STAT.CHANNEL_ID.as(CHANNEL_ID));
            groupFields.add(LOAN_GALAXY_CARD_STAT.CHANNEL_ID);
            condition = condition.and(LOAN_GALAXY_CARD_STAT.CHANNEL_ID.in(searchRequest.getChannelId()));
        }
        else
            selectFields.add(DSL.field(ALL_FIELD).as(CHANNEL_ID));

        if (Objects.nonNull(searchRequest.getPlatform()) && !searchRequest.getPlatform().isEmpty()){
            selectFields.add(LOAN_GALAXY_CARD_STAT.PLATFORM.as(PLATFORM));
            groupFields.add(LOAN_GALAXY_CARD_STAT.PLATFORM);
            condition = condition.and(LOAN_GALAXY_CARD_STAT.PLATFORM.in(searchRequest.getPlatform()));
        }
        else
            selectFields.add(DSL.field(ALL_FIELD).as(PLATFORM));

        condition = condition.and(LOAN_GALAXY_CARD_STAT.TYPE.eq(type));

        if (Objects.nonNull(searchRequest.getStartTime()))
            condition = condition.and(LOAN_GALAXY_CARD_STAT.CREATE_DATE.ge(new Date(searchRequest.getStartTime().getTime())));
        if (Objects.nonNull(searchRequest.getEndTime()))
            condition = condition.and(LOAN_GALAXY_CARD_STAT.CREATE_DATE.le(new Date(searchRequest.getEndTime().getTime())));

        return dslContext.select(selectFields)
                .from(LOAN_GALAXY_CARD_STAT)
                .where(condition)
                .groupBy(groupFields)
                .orderBy(LOAN_GALAXY_CARD_STAT.CREATE_DATE.desc())
                .getQuery();
    }

    @Override
    protected TableImpl getTable() {
        return LOAN_GALAXY_CARD_STAT;
    }
}